package com.cn.wanxi.dao.impl;

import com.cn.wanxi.dao.UserDao;
import com.cn.wanxi.model.UserModel;
import com.cn.wanxi.util.JDBC;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class UserImpl implements UserDao {

    /**
     * 第一种：只增加一条数据
     * 第二种：增加多条数据
     *
     * @return
     */
    public int add(UserModel userModel) {
////        如果插入数据需要几步？
////        1.要用到mysql，就必须下载mysql包，将包加载到项目里面
//        try {
//            Class.forName("com.mysql.cj.jdbc.Driver");
//        } catch (ClassNotFoundException e) {
//            e.printStackTrace();
//        }
////        2.得到数据库的连接
//        String url = "jdbc:mysql://121.199.55.96:3306/canyin04?serviceTimezone=UTC&characterEncoding=UTF-8";
//        String user = "canyin04";
//        String pass = "123456";
//        Connection connection = null;
//        try {
//            connection = DriverManager.getConnection(url, user, pass);
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }
////        System.out.println(connection);
////        3.编译sql语句
        String sql = "INSERT INTO `user` (" +
                "`username`, `address`, `create_time`, " +
                "`email`, `enable`, `password`," +
                " `phone`, `sex`, `update_time`," +
                " `hobby`, `birthday`, `remark`) VALUES (" +
                "'" + userModel.getUsername() + "', '" + userModel.getAddress() + "', now()," +
                " '" + userModel.getEmail() + "', '" + userModel.getEnable() + "', '" + userModel.getPassword() + "'," +
                " '" + userModel.getPhone() + "', '" + userModel.getSex() + "', sysdate()," +
                " '" + userModel.getHobby() + "', '" + userModel.getBirthday() + "', '');";
//        PreparedStatement preparedStatement = null;
//        int count = 0;
//        try {
//            preparedStatement = connection.prepareStatement(sql);
////            4.执行sql语句，并返回影响的条数
//            count = preparedStatement.executeUpdate();
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }
//        System.out.println(count);
        return JDBC.update(sql);
    }

    @Override
    public int delete(UserModel userModel) {
        String sql = "delete from user where id=" + userModel.getId();
        return JDBC.update(sql);
    }

    @Override
    public int getCount(UserModel userModel) {
        String sql = "select count(*) count from user where 1=1 ";
        sql += getSql(userModel);

        return JDBC.getCount(sql);
    }

    public String getSql(UserModel userModel) {
        StringBuilder sql = new StringBuilder();
        if (!"".equals(userModel.getUsername())) {
            sql.append(" and  username like '%").append(userModel.getUsername()).append("%'");
        }
        if (!"".equals(userModel.getPhone())) {
            sql.append(" and phone like '%").append(userModel.getPhone()).append("%'");
        }
        if (!"".equals(userModel.getSex())) {
            sql.append(" and sex = '").append(userModel.getSex()).append("'");
        }
        if (userModel.getEnable() != -1) {
            sql.append(" and enable = '").append(userModel.getEnable()).append("'");
        }
//        if(start!=null&&start!=""  && (end ==null||end=="")){
//            sql+=" and birthday >="+start;
//        }
        if (!"".equals(userModel.getStart()) && "".equals(userModel.getEnd())) {
            sql.append(" and birthday >='").append(userModel.getStart()).append("'");
        }
        if ("".equals(userModel.getStart()) && !"".equals(userModel.getEnd())) {
            sql.append(" and birthday <='").append(userModel.getEnd()).append("'");
        }
        if (!"".equals(userModel.getStart()) && !"".equals(userModel.getEnd())) {
            sql.append(" and birthday between '").append(userModel.getStart()).append("' and '").append(userModel.getEnd()).append("'");
        }
        if (!"".equals(userModel.getHobby())) {
            String[] hobbys = userModel.getHobby().split(",");
            for (String hobby : hobbys) {
                sql.append(" and find_in_set('").append(hobby).append("',hobby)");
            }

        }
        return sql.toString();
    }

    public int update() {
//        //        如果插入数据需要几步？
////        1.要用到mysql，就必须下载mysql包，将包加载到项目里面
//        try {
//            Class.forName("com.mysql.cj.jdbc.Driver");
//        } catch (ClassNotFoundException e) {
//            e.printStackTrace();
//        }
////        2.得到数据库的连接
//        String url = "jdbc:mysql://121.199.55.96:3306/canyin04?serviceTimezone=UTC&characterEncoding=UTF-8";
//        String user = "canyin04";
//        String pass = "123456";
//        Connection connection = null;
//        try {
//            connection = DriverManager.getConnection(url, user, pass);
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }
////        System.out.println(connection);
////        3.编译sql语句
        String sql = "UPDATE USER SET USERNAME ='BBBBBB' WHERE ID=250";
//        PreparedStatement preparedStatement = null;
//        int count = 0;
//        try {
//            preparedStatement = connection.prepareStatement(sql);
////            4.执行sql语句，并返回影响的条数
//            count = preparedStatement.executeUpdate();
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }
//        System.out.println(count);
        return JDBC.update(sql);
    }


    public UserModel findById(int id) {

//        //        如果插入数据需要几步？
////        1.要用到mysql，就必须下载mysql包，将包加载到项目里面
//        try {
//            Class.forName("com.mysql.cj.jdbc.Driver");
//        } catch (ClassNotFoundException e) {
//            e.printStackTrace();
//        }
////        2.得到数据库的连接
//        String url = "jdbc:mysql://121.199.55.96:3306/canyin04?serviceTimezone=UTC&characterEncoding=UTF-8";
//        String username = "canyin04";
//        String pass = "123456";
//        Connection connection = null;
//        try {
//            connection = DriverManager.getConnection(url, username, pass);
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }
////        System.out.println(connection);
////        3.编译sql语句
        String sql = "select * from user where id=" + id;
//        PreparedStatement preparedStatement = null;
////        ResultSet理解为数据库的查询结果
//        ResultSet resultSet = null;
//        try {
//            preparedStatement = connection.prepareStatement(sql);
////            4.执行sql语句，并返回查询的结果
//            resultSet = preparedStatement.executeQuery();
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }
//        User user = new User();
//        try {
////           如果resultSet里面有值，则进入while循环，否则不进入
////           所以，我们需要将ResultSet进行解析
//            while (resultSet.next()) {
////                user.setUsername对user进行赋值
////                resultSet.getString拿到resultSet里面的值，通过username来拿
//                user.setUsername(resultSet.getString("username"));
//                user.setBirthday(resultSet.getString("birthday"));
//                user.setCreateTime(resultSet.getString("create_time"));
//
//            }
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }
//        return user;
        ResultSet resultSet = JDBC.query(sql);
        UserModel user = new UserModel();
        try {
//           如果resultSet里面有值，则进入while循环，否则不进入
//           所以，我们需要将ResultSet进行解析
            while (resultSet.next()) {
//                user.setUsername对user进行赋值
//                resultSet.getString拿到resultSet里面的值，通过username来拿
                user.setUsername(resultSet.getString("username"));
                user.setBirthday(resultSet.getString("birthday"));
                user.setCreateTime(resultSet.getString("create_time"));
                user.setId(resultSet.getInt("id"));
                user.setPhone(resultSet.getString("phone"));
                user.setSex(resultSet.getString("sex"));
                user.setEnable(resultSet.getInt("enable"));
                user.setAddress(resultSet.getString("address"));
                user.setHobby(resultSet.getString("hobby"));
                user.setEmail(resultSet.getString("email"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return user;
    }

    public List<UserModel> findAll(UserModel userModel) {
        List<UserModel> list = new ArrayList<>();
        String sql = " select * from user where 1=1 ";
        sql += getSql(userModel);


        sql += " order by id desc ";
        sql += " limit " + (userModel.getPage() - 1) * userModel.getLimit() + "," + userModel.getLimit();
        ResultSet resultSet = JDBC.query(sql);
        try {
            while (resultSet.next()) {
                UserModel user = new UserModel();
                user.setUsername(resultSet.getString("username"));
                user.setBirthday(resultSet.getString("birthday"));
                user.setCreateTime(resultSet.getString("create_time"));
                user.setId(resultSet.getInt("id"));
                user.setPhone(resultSet.getString("phone"));
                user.setSex(resultSet.getString("sex"));
                user.setEnable(resultSet.getInt("enable"));
                user.setAddress(resultSet.getString("address"));
                user.setHobby(resultSet.getString("hobby"));
                user.setEmail(resultSet.getString("email"));
//                一定记得把对象放入到list里面去
                list.add(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public int update(UserModel userModel) {
//        String sql="update user set " +
//                " username ="+userModel.getUsername()+"," +
//                " phone ="+userModel.getPhone()+" " +
//                " where id="+userModel.getId();
//        int a = 0;
//        String aa = "";
//        if (a > 0) {
//            aa = "a";
//        } else {
//            aa = "b";
//        }
////        三元运算符
//        aa = a > 0 ? "a" : "b";
        String sql = "UPDATE `user` SET ";
        if (!"".equals(userModel.getUsername())) {
            sql += "`username` = '" + userModel.getUsername() + "',";
        }


        sql += " `address` = '" + userModel.getAddress() + "', " +
                " `email` = '" + userModel.getEmail() + "', " +
                " `enable` = '" + userModel.getEnable() + "'," +
                " `phone` = '" + userModel.getPhone() + "'," +
                " `sex` = '" + userModel.getSex() + "', " +
                " `update_time` =now(), " +
                " `hobby` = '" + (userModel.getHobby() == null ? "" : userModel.getHobby()) + "'," +
                " `birthday` = '" + userModel.getBirthday() + "'," +
                " `remark` = '' WHERE" +
                " (`id` = '" + userModel.getId() + "');";
        return JDBC.update(sql);
    }

    @Override
    public UserModel findById(UserModel userModel) {
        String sql = "select * from user where id=" + userModel.getId();
        ResultSet resultSet = JDBC.query(sql);
        UserModel user = null;
        try {
            while (resultSet.next()) {
                user = new UserModel();
                user.setUsername(resultSet.getString("username"));
                user.setBirthday(resultSet.getString("birthday"));
                user.setCreateTime(resultSet.getString("create_time"));
                user.setId(resultSet.getInt("id"));
                user.setPhone(resultSet.getString("phone"));
                user.setSex(resultSet.getString("sex"));
                user.setEnable(resultSet.getInt("enable"));
                user.setAddress(resultSet.getString("address"));
                user.setHobby(resultSet.getString("hobby"));
                user.setEmail(resultSet.getString("email"));
//                一定记得把对象放入到list里面去

            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return user;
    }

    @Override
    public UserModel findByUsernameAndPassword(UserModel userModel) {
        UserModel model = null;
        String sql = "select * from user where username='" + userModel.getUsername() + "'" +
                " and password='" + userModel.getPassword() + "'";
        ResultSet resultSet = JDBC.query(sql);
        try {
            while (resultSet.next()) {
                model = new UserModel();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return model;
    }
}
